#include "analysiswidget.h"
#include <QVBoxLayout>
#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>
#include <algorithm>
#include <QSharedPointer>
#include "databasemanager.h"

analysiswidget::analysiswidget(QWidget *parent) : QWidget(parent)
{
    QVBoxLayout *layout = new QVBoxLayout(this);

    // 初始化6个图表控件
    customPlotVisitorTrend = new QCustomPlot(this);
    customPlotVenueDistribution = new QCustomPlot(this);
    customPlotAgeHistogram = new QCustomPlot(this);
    customPlotSatisfaction = new QCustomPlot(this);
    customPlotOccupationPreference = new QCustomPlot(this);
    customPlotVenueTypeDistribution = new QCustomPlot(this);

    // 加载数据并绘图
    loadVisitorTrendFromDatabase();
    loadVenueDistributionFromDatabase();
    loadAgeHistogramFromDatabase();
    loadSatisfactionBarPlotFromDatabase();
    loadOccupationPreferenceFromDatabase();
    loadVenueTypeDistributionFromDatabase();

    // 将图表依次加入布局，比例为1，均分空间
    layout->addWidget(customPlotVisitorTrend, 1);
    layout->addWidget(customPlotVenueDistribution, 1);
    layout->addWidget(customPlotAgeHistogram, 1);
    layout->addWidget(customPlotSatisfaction, 1);
    layout->addWidget(customPlotOccupationPreference, 1);
    layout->addWidget(customPlotVenueTypeDistribution, 1);

    setLayout(layout);
}

analysiswidget::~analysiswidget()
{
    // QCustomPlot由Qt父对象机制自动释放，无需手动删除
}

// 1. 访客流量趋势（按月）
void analysiswidget::loadVisitorTrendFromDatabase()
{
    QString sql = R"(
        SELECT strftime('%m', visit_date) AS month, COUNT(*) AS visitor_count
        FROM Venue_Visits
        GROUP BY month
        ORDER BY month
    )";

    QSqlQuery query(DatabaseManager::instance().getDatabase());

    QVector<double> x, y;

    if (!query.exec(sql)) {
        qDebug() << "访客流量趋势查询失败:" << query.lastError().text();
        setupVisitorTrendPlotDefault();
        return;
    }

    while (query.next()) {
        x.append(query.value("month").toInt());
        y.append(query.value("visitor_count").toDouble());
    }

    if (x.isEmpty()) {
        qDebug() << "访客流量趋势数据为空，使用默认数据";
        setupVisitorTrendPlotDefault();
        return;
    }

    customPlotVisitorTrend->clearGraphs();
    customPlotVisitorTrend->addGraph();
    customPlotVisitorTrend->graph(0)->setData(x, y);
    customPlotVisitorTrend->graph(0)->setPen(QPen(Qt::blue));
    customPlotVisitorTrend->graph(0)->setScatterStyle(QCPScatterStyle::ssCircle);

    customPlotVisitorTrend->xAxis->setLabel("月份");
    customPlotVisitorTrend->yAxis->setLabel("访客数量");
    customPlotVisitorTrend->xAxis->setRange(1, 12);
    double yMax = *std::max_element(y.constBegin(), y.constEnd());
    customPlotVisitorTrend->yAxis->setRange(0, yMax * 1.2);

    QSharedPointer<QCPAxisTickerText> textTicker(new QCPAxisTickerText);
    QStringList months{"1月","2月","3月","4月","5月","6月","7月","8月","9月","10月","11月","12月"};
    for (int i = 0; i < months.size(); ++i)
        textTicker->addTick(i + 1, months.at(i));
    customPlotVisitorTrend->xAxis->setTicker(textTicker);
    customPlotVisitorTrend->xAxis->setTickLabelRotation(45);

    customPlotVisitorTrend->replot();
}

void analysiswidget::setupVisitorTrendPlotDefault()
{
    // 备用默认数据
    QVector<double> x(12), y(12);
    for (int i = 0; i < 12; ++i) {
        x[i] = i + 1;
        y[i] = 50 + i * 10;  // 简单递增数据
    }

    customPlotVisitorTrend->clearGraphs();
    customPlotVisitorTrend->addGraph();
    customPlotVisitorTrend->graph(0)->setData(x, y);
    customPlotVisitorTrend->graph(0)->setPen(QPen(Qt::blue));
    customPlotVisitorTrend->graph(0)->setScatterStyle(QCPScatterStyle::ssCircle);

    customPlotVisitorTrend->xAxis->setLabel("月份");
    customPlotVisitorTrend->yAxis->setLabel("访客数量");
    customPlotVisitorTrend->xAxis->setRange(1, 12);
    customPlotVisitorTrend->yAxis->setRange(0, 150);

    QSharedPointer<QCPAxisTickerText> textTicker(new QCPAxisTickerText);
    QStringList months{"1月","2月","3月","4月","5月","6月","7月","8月","9月","10月","11月","12月"};
    for (int i = 0; i < months.size(); ++i)
        textTicker->addTick(i + 1, months.at(i));
    customPlotVisitorTrend->xAxis->setTicker(textTicker);
    customPlotVisitorTrend->xAxis->setTickLabelRotation(45);

    customPlotVisitorTrend->replot();
}

// 2. 场馆访问量分布（柱状图）
void analysiswidget::loadVenueDistributionFromDatabase()
{
    QString sql = R"(
        SELECT Venues.name, COUNT(*) AS visit_count
        FROM Venue_Visits
        JOIN Venues ON Venue_Visits.venue_id = Venues.venue_id
        GROUP BY Venue_Visits.venue_id
        ORDER BY visit_count DESC
    )";

    QSqlQuery query(DatabaseManager::instance().getDatabase());

    QVector<double> ticks;
    QVector<double> values;
    QStringList labels;

    if (!query.exec(sql)) {
        qDebug() << "场馆访问量查询失败:" << query.lastError().text();
        setupVenueDistributionPlotDefault();
        return;
    }

    int index = 1;
    while (query.next()) {
        labels.append(query.value("name").toString());
        values.append(query.value("visit_count").toDouble());
        ticks.append(index++);
    }

    if (labels.isEmpty()) {
        qDebug() << "场馆访问量数据为空，使用默认数据";
        setupVenueDistributionPlotDefault();
        return;
    }

    customPlotVenueDistribution->clearPlottables();

    QCPBars *bars = new QCPBars(customPlotVenueDistribution->xAxis, customPlotVenueDistribution->yAxis);
    bars->setWidth(0.6);
    bars->setData(ticks, values);

    QSharedPointer<QCPAxisTickerText> textTicker(new QCPAxisTickerText);
    for (int i = 0; i < labels.size(); ++i)
        textTicker->addTick(ticks[i], labels.at(i));
    customPlotVenueDistribution->xAxis->setTicker(textTicker);

    customPlotVenueDistribution->xAxis->setLabel("场馆");
    customPlotVenueDistribution->yAxis->setLabel("访问量");

    double maxVal = values.isEmpty() ? 50 : *std::max_element(values.constBegin(), values.constEnd());
    customPlotVenueDistribution->yAxis->setRange(0, maxVal * 1.2);

    customPlotVenueDistribution->xAxis->setRange(0, labels.size() + 1);

    customPlotVenueDistribution->replot();
}

void analysiswidget::setupVenueDistributionPlotDefault()
{
    QVector<double> values{40, 30, 15, 10, 5};
    QStringList labels{"博物馆", "美术馆", "科技馆", "历史馆", "儿童馆"};

    QVector<double> ticks;
    for (int i = 0; i < values.size(); ++i)
        ticks.append(i + 1);

    customPlotVenueDistribution->clearPlottables();

    QCPBars *bars = new QCPBars(customPlotVenueDistribution->xAxis, customPlotVenueDistribution->yAxis);
    bars->setWidth(0.6);
    bars->setData(ticks, values);

    QSharedPointer<QCPAxisTickerText> textTicker(new QCPAxisTickerText);
    for (int i = 0; i < labels.size(); ++i)
        textTicker->addTick(ticks[i], labels.at(i));
    customPlotVenueDistribution->xAxis->setTicker(textTicker);

    customPlotVenueDistribution->xAxis->setLabel("场馆");
    customPlotVenueDistribution->yAxis->setLabel("访问量");
    customPlotVenueDistribution->yAxis->setRange(0, 50);
    customPlotVenueDistribution->xAxis->setRange(0, labels.size() + 1);

    customPlotVenueDistribution->replot();
}

// 3. 访客年龄分布直方图
void analysiswidget::loadAgeHistogramFromDatabase()
{
    QString sql = R"(
        SELECT age_group, COUNT(*) AS visitor_count
        FROM Venue_Visits
        JOIN Visitors ON Venue_Visits.visitor_id = Visitors.visitor_id
        GROUP BY age_group
        ORDER BY age_group
    )";

    QSqlQuery query(DatabaseManager::instance().getDatabase());

    QVector<double> ticks;
    QVector<double> counts;
    QStringList ageLabels;

    if (!query.exec(sql)) {
        qDebug() << "访客年龄分布查询失败:" << query.lastError().text();
        setupAgeHistogramPlotDefault();
        return;
    }

    int idx = 0;
    while (query.next()) {
        ageLabels.append(query.value("age_group").toString());
        counts.append(query.value("visitor_count").toDouble());
        ticks.append(idx++);
    }

    if (ageLabels.isEmpty()) {
        qDebug() << "访客年龄分布数据为空，使用默认数据";
        setupAgeHistogramPlotDefault();
        return;
    }

    customPlotAgeHistogram->clearPlottables();

    QCPBars *bars = new QCPBars(customPlotAgeHistogram->xAxis, customPlotAgeHistogram->yAxis);
    bars->setWidth(0.8);
    bars->setData(ticks, counts);

    QSharedPointer<QCPAxisTickerText> textTicker(new QCPAxisTickerText);
    for (int i = 0; i < ageLabels.size(); ++i)
        textTicker->addTick(ticks[i], ageLabels.at(i));
    customPlotAgeHistogram->xAxis->setTicker(textTicker);

    customPlotAgeHistogram->xAxis->setLabel("年龄段");
    customPlotAgeHistogram->yAxis->setLabel("人数");

    double maxVal = counts.isEmpty() ? 80 : *std::max_element(counts.constBegin(), counts.constEnd());
    customPlotAgeHistogram->yAxis->setRange(0, maxVal * 1.2);

    customPlotAgeHistogram->xAxis->setRange(-0.5, ageLabels.size() - 0.5);

    customPlotAgeHistogram->replot();
}

void analysiswidget::setupAgeHistogramPlotDefault()
{
    QVector<double> ticks{0, 1, 2, 3, 4, 5, 6, 7};
    QVector<double> counts{5, 15, 40, 70, 30, 20, 10, 5};
    QStringList ageLabels{"0-10","10-20","20-30","30-40","40-50","50-60","60-70","70-80"};

    customPlotAgeHistogram->clearPlottables();

    QCPBars *bars = new QCPBars(customPlotAgeHistogram->xAxis, customPlotAgeHistogram->yAxis);
    bars->setWidth(0.8);
    bars->setData(ticks, counts);

    QSharedPointer<QCPAxisTickerText> textTicker(new QCPAxisTickerText);
    for (int i = 0; i < ageLabels.size(); ++i)
        textTicker->addTick(ticks[i], ageLabels.at(i));
    customPlotAgeHistogram->xAxis->setTicker(textTicker);

    customPlotAgeHistogram->xAxis->setLabel("年龄段");
    customPlotAgeHistogram->yAxis->setLabel("人数");
    customPlotAgeHistogram->yAxis->setRange(0, 80);
    customPlotAgeHistogram->xAxis->setRange(-0.5, ageLabels.size() - 0.5);

    customPlotAgeHistogram->replot();
}

// 4. 访客满意度分布柱状图
void analysiswidget::loadSatisfactionBarPlotFromDatabase()
{
    QString sql = R"(
        SELECT satisfaction_rating, COUNT(*) AS count
        FROM Venue_Visits
        WHERE satisfaction_rating IS NOT NULL
        GROUP BY satisfaction_rating
        ORDER BY satisfaction_rating
    )";

    QSqlQuery query(DatabaseManager::instance().getDatabase());

    QVector<double> ratings;
    QVector<double> counts;
    QStringList ratingLabels;

    if (!query.exec(sql)) {
        qDebug() << "满意度分布查询失败:" << query.lastError().text();
        setupSatisfactionBarPlotDefault();
        return;
    }

    while (query.next()) {
        int rating = query.value("satisfaction_rating").toInt();
        double count = query.value("count").toDouble();
        ratings.append(rating);
        counts.append(count);
        ratingLabels.append(QString::number(rating) + "星");
    }

    if (ratings.isEmpty()) {
        qDebug() << "满意度数据为空，使用默认数据";
        setupSatisfactionBarPlotDefault();
        return;
    }

    customPlotSatisfaction->clearPlottables();

    QCPBars *bars = new QCPBars(customPlotSatisfaction->xAxis, customPlotSatisfaction->yAxis);
    bars->setWidth(0.6);
    bars->setData(ratings, counts);

    QSharedPointer<QCPAxisTickerText> textTicker(new QCPAxisTickerText);
    for (int i = 0; i < ratingLabels.size(); ++i)
        textTicker->addTick(ratings[i], ratingLabels.at(i));
    customPlotSatisfaction->xAxis->setTicker(textTicker);

    customPlotSatisfaction->xAxis->setLabel("满意度评分");
    customPlotSatisfaction->yAxis->setLabel("人数");

    double maxVal = counts.isEmpty() ? 40 : *std::max_element(counts.constBegin(), counts.constEnd());
    customPlotSatisfaction->yAxis->setRange(0, maxVal * 1.2);

    customPlotSatisfaction->xAxis->setRange(0, 6);

    customPlotSatisfaction->replot();
}

void analysiswidget::setupSatisfactionBarPlotDefault()
{
    QVector<double> ratingBins{1, 2, 3, 4, 5};
    QVector<double> counts{10, 15, 35, 20, 20};
    QStringList ratingLabels{"1星","2星","3星","4星","5星"};

    customPlotSatisfaction->clearPlottables();

    QCPBars *bars = new QCPBars(customPlotSatisfaction->xAxis, customPlotSatisfaction->yAxis);
    bars->setWidth(0.6);
    bars->setData(ratingBins, counts);

    QSharedPointer<QCPAxisTickerText> textTicker(new QCPAxisTickerText);
    for (int i = 0; i < ratingLabels.size(); ++i)
        textTicker->addTick(ratingBins[i], ratingLabels.at(i));
    customPlotSatisfaction->xAxis->setTicker(textTicker);

    customPlotSatisfaction->xAxis->setLabel("满意度评分");
    customPlotSatisfaction->yAxis->setLabel("人数");
    customPlotSatisfaction->yAxis->setRange(0, 40);
    customPlotSatisfaction->xAxis->setRange(0, 6);

    customPlotSatisfaction->replot();
}
void analysiswidget::loadOccupationPreferenceFromDatabase()
{
    QString sql = R"(
        SELECT Visitors.occupation_type, COUNT(*) AS visit_count
        FROM Venue_Visits
        JOIN Visitors ON Venue_Visits.visitor_id = Visitors.visitor_id
        GROUP BY Visitors.occupation_type
        ORDER BY visit_count DESC
    )";

    QSqlQuery query(DatabaseManager::instance().getDatabase());

    QVector<double> ticks;
    QVector<double> values;
    QStringList labels;

    if (!query.exec(sql)) {
        qDebug() << "职业访客访问偏好查询失败:" << query.lastError().text();
        setupOccupationPreferencePlotDefault();
        return;
    }

    int index = 1;
    while (query.next()) {
        labels.append(query.value("occupation_type").toString());
        values.append(query.value("visit_count").toDouble());
        ticks.append(index++);
    }

    if (labels.isEmpty()) {
        qDebug() << "职业访客访问偏好数据为空，使用默认数据";
        setupOccupationPreferencePlotDefault();
        return;
    }

    customPlotOccupationPreference->clearPlottables();

    QCPBars *bars = new QCPBars(customPlotOccupationPreference->xAxis, customPlotOccupationPreference->yAxis);
    bars->setWidth(0.6);
    bars->setData(ticks, values);

    QSharedPointer<QCPAxisTickerText> textTicker(new QCPAxisTickerText);
    for (int i = 0; i < labels.size(); ++i)
        textTicker->addTick(ticks[i], labels.at(i));
    customPlotOccupationPreference->xAxis->setTicker(textTicker);

    customPlotOccupationPreference->xAxis->setLabel("职业类型");
    customPlotOccupationPreference->yAxis->setLabel("访问次数");

    double maxVal = values.isEmpty() ? 50 : *std::max_element(values.constBegin(), values.constEnd());
    customPlotOccupationPreference->yAxis->setRange(0, maxVal * 1.2);
    customPlotOccupationPreference->xAxis->setRange(0, labels.size() + 1);

    customPlotOccupationPreference->replot();
}

void analysiswidget::setupOccupationPreferencePlotDefault()
{
    QVector<double> values{50, 30, 20, 15, 10};
    QStringList labels{"学生", "教师", "公务员", "自由职业", "其他"};
    QVector<double> ticks;
    for (int i = 0; i < values.size(); ++i)
        ticks.append(i + 1);

    customPlotOccupationPreference->clearPlottables();

    QCPBars *bars = new QCPBars(customPlotOccupationPreference->xAxis, customPlotOccupationPreference->yAxis);
    bars->setWidth(0.6);
    bars->setData(ticks, values);

    QSharedPointer<QCPAxisTickerText> textTicker(new QCPAxisTickerText);
    for (int i = 0; i < labels.size(); ++i)
        textTicker->addTick(ticks[i], labels.at(i));
    customPlotOccupationPreference->xAxis->setTicker(textTicker);

    customPlotOccupationPreference->xAxis->setLabel("职业类型");
    customPlotOccupationPreference->yAxis->setLabel("访问次数");
    customPlotOccupationPreference->yAxis->setRange(0, 60);
    customPlotOccupationPreference->xAxis->setRange(0, labels.size() + 1);

    customPlotOccupationPreference->replot();
}

// 6. 场馆类型访问量分布（柱状图）
void analysiswidget::loadVenueTypeDistributionFromDatabase()
{
    QString sql = R"(
        SELECT Venues.type, COUNT(*) AS visit_count
        FROM Venue_Visits
        JOIN Venues ON Venue_Visits.venue_id = Venues.venue_id
        GROUP BY Venues.type
        ORDER BY visit_count DESC
    )";

    QSqlQuery query(DatabaseManager::instance().getDatabase());

    QVector<double> ticks;
    QVector<double> values;
    QStringList labels;

    if (!query.exec(sql)) {
        qDebug() << "场馆类型访问量查询失败:" << query.lastError().text();
        setupVenueTypeDistributionPlotDefault();
        return;
    }

    int index = 1;
    while (query.next()) {
        labels.append(query.value("type").toString());
        values.append(query.value("visit_count").toDouble());
        ticks.append(index++);
    }

    if (labels.isEmpty()) {
        qDebug() << "场馆类型访问量数据为空，使用默认数据";
        setupVenueTypeDistributionPlotDefault();
        return;
    }

    customPlotVenueTypeDistribution->clearPlottables();

    QCPBars *bars = new QCPBars(customPlotVenueTypeDistribution->xAxis, customPlotVenueTypeDistribution->yAxis);
    bars->setWidth(0.6);
    bars->setData(ticks, values);

    QSharedPointer<QCPAxisTickerText> textTicker(new QCPAxisTickerText);
    for (int i = 0; i < labels.size(); ++i)
        textTicker->addTick(ticks[i], labels.at(i));
    customPlotVenueTypeDistribution->xAxis->setTicker(textTicker);

    customPlotVenueTypeDistribution->xAxis->setLabel("场馆类型");
    customPlotVenueTypeDistribution->yAxis->setLabel("访问量");

    double maxVal = values.isEmpty() ? 50 : *std::max_element(values.constBegin(), values.constEnd());
    customPlotVenueTypeDistribution->yAxis->setRange(0, maxVal * 1.2);
    customPlotVenueTypeDistribution->xAxis->setRange(0, labels.size() + 1);

    customPlotVenueTypeDistribution->replot();
}

void analysiswidget::setupVenueTypeDistributionPlotDefault()
{
    QVector<double> values{40, 30, 15, 10, 5};
    QStringList labels{"博物馆", "美术馆", "科技馆", "历史馆", "儿童馆"};
    QVector<double> ticks;
    for (int i = 0; i < values.size(); ++i)
        ticks.append(i + 1);

    customPlotVenueTypeDistribution->clearPlottables();

    QCPBars *bars = new QCPBars(customPlotVenueTypeDistribution->xAxis, customPlotVenueTypeDistribution->yAxis);
    bars->setWidth(0.6);
    bars->setData(ticks, values);

    QSharedPointer<QCPAxisTickerText> textTicker(new QCPAxisTickerText);
    for (int i = 0; i < labels.size(); ++i)
        textTicker->addTick(ticks[i], labels.at(i));
    customPlotVenueTypeDistribution->xAxis->setTicker(textTicker);

    customPlotVenueTypeDistribution->xAxis->setLabel("场馆类型");
    customPlotVenueTypeDistribution->yAxis->setLabel("访问量");
    customPlotVenueTypeDistribution->yAxis->setRange(0, 50);
    customPlotVenueTypeDistribution->xAxis->setRange(0, labels.size() + 1);

    customPlotVenueTypeDistribution->replot();
}
